package fr.esisar.picaso.requetes;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import fr.esisar.picaso.entites.Adresse;
import fr.esisar.picaso.moteur.Connexion;

public class RequetesAdresse {
	public RequetesAdresse() {
		
	}
	
	public Adresse getAdresse(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT * FROM U0.adresse WHERE idAdresse="+id;
		ResultSet rs = st.executeQuery(requete);
		
		Adresse adresse = null;
		while(rs.next()) {
			adresse = new Adresse(id, rs.getString("numero"), rs.getString("rue"), rs.getString("ville"), rs.getString("pays"), rs.getInt("cp"));
		}
		
		return adresse;
	}
	
	public void setAdresse(Adresse adresse) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.adresse SET numero='"+adresse.getNumero()+"', rue='"+adresse.getRue()+"', ville='"+adresse.getVille()+"', pays='"+adresse.getPays()+"', cp="+adresse.getCode_postal()+" WHERE idAdresse="+adresse.getId();
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public void setAdresse(int id, String numero, String rue, String ville, String pays, int cp) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.adresse SET numero='"+numero+"', rue='"+rue+"', ville='"+ville+"', pays='"+pays+"', cp="+cp+" WHERE idAdresse="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public String getNumero(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT numero FROM U0.adresse WHERE idAdresse="+id;
		ResultSet rs = st.executeQuery(requete);
		
		String retour = null;
		while(rs.next()) {
			retour = new String(rs.getString("numero"));
		}
		
		return retour;
	}
	
	public void setNumero(int id, String numero) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.adresse SET numero='"+numero+"' WHERE idAdresse="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public String getRue(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT rue FROM U0.adresse WHERE id="+id;
		ResultSet rs = st.executeQuery(requete);
		
		String retour = null;
		while(rs.next()) {
			retour = new String(rs.getString("rue"));
		}
		
		return retour;
	}
	
	public void setRue(int id, String rue) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.adresse SET rue='"+rue+"' WHERE idAdresse="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public String getVille(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT ville FROM U0.adresse WHERE idAdresse="+id;
		ResultSet rs = st.executeQuery(requete);
		
		String retour = null;
		while(rs.next()) {
			retour = new String(rs.getString("ville"));
		}
		
		return retour;
	}
	
	public void setVille(int id, String ville) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.adresse SET ville='"+ville+"' WHERE idAdresse="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public String getPays(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT pays FROM U0.adresse WHERE idAdresse="+id;
		ResultSet rs = st.executeQuery(requete);
		
		String retour = null;
		while(rs.next()) {
			retour = new String(rs.getString("pays"));
		}
		
		return retour;
	}
	
	public void setPays(int id, String pays) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.adresse SET pays='"+pays+"' WHERE idAdresse="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
	
	public int getCp(int id) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "SELECT cp FROM U0.adresse WHERE idAdresse="+id;
		ResultSet rs = st.executeQuery(requete);
		
		int retour = 0;
		while(rs.next()) {
			retour = rs.getInt("cp");
		}
		
		return retour;
	}
	
	public void setCp(int id, int cp) throws SQLException {
		Connection connexion = Connexion.getConnection();
		Statement st = connexion.createStatement();
		
		String requete = "UPDATE U0.adresse SET cp="+cp+" WHERE idAdresse="+id;
		st.executeQuery(requete);
		connexion.commit();
	}
}
